clear all
set more off
#delimit;
set excelxlsxlargefile on;


*cd "C:\Users\lkarabar\Dropbox\EURO-PROD\Model_Loukas\codes_QJE";
import excel dataset.xlsx, sheet("Sheet1") clear;


*cd "C:\Users\lkarabar\Dropbox\EURO-PROD\Model_Loukas\codes_QJE\saved-datasets";
*import excel dataset_HeF.xlsx, sheet("Sheet1") clear;
*import excel dataset_HoF.xlsx, sheet("Sheet1") clear;
*import excel dataset_NoF.xlsx, sheet("Sheet1") clear;
*import excel dataset_HeF_robustness_row5.xlsx, sheet("Sheet1") clear;
*import excel dataset_HeF_robustness_row6.xlsx, sheet("Sheet1") clear;
*import excel dataset_HeF_robustness_row7.xlsx, sheet("Sheet1") clear;
*import excel dataset_HeF_robustness_row8.xlsx, sheet("Sheet1") clear;
*import excel dataset_HeF_robustness_row9.xlsx, sheet("Sheet1") clear;
*cd "C:\Users\lkarabar\Dropbox\EURO-PROD\Model_Loukas\codes_QJE";


*name variables in sheets;
qui rename A Tshock;
qui rename B idn;
qui rename C time;
qui rename D k;
qui rename E a;
qui rename F zt;
qui rename G zp;
qui rename H l;
qui rename I y;
qui rename J mrpk;
qui rename K rev;
qui rename L constrained;
qui rename M r;
qui rename N logTFPobserved;
qui rename O MIS;
qui rename P RR;
qui rename Q yy;
qui rename R YY;
qui rename S KK;
qui rename T LL;
qui rename U AA_assets;
qui rename V mrpl;
qui rename W Psi;
qui rename X c;
qui rename Y lambda;
qui rename Z CC;
qui rename AA fraction_constrained;
qui rename AA_assets AA;
qui rename AB std_logMRPK;
qui rename AC q;

*time variables;
qui replace Tshock=1994;
qui replace time=time-800+Tshock;
xtset idn time, yearly;
qui gen inregression=0;
qui replace inregression=1 if time>=1999 & time<=2007;

*generate other variables;
qui gen logprod=zt+log(zp);
qui gen b = k - a;
qui gen borrower=0;
qui replace borrower=1 if b>0;
qui gen BB=KK-AA;
qui gen logMRPK = log(mrpk);
qui gen loga=log(a);
qui gen logk=log(k);
qui gen logl=log(l);
qui gen leverage=b/k;

foreach var in "logprod" "k" "logk" "loga" "b"{;
qui gen `var'_regression=`var' if inregression==1;
};
bys idn: gen DeltaFkk_regression=(F.k_regression-k_regression)/k_regression;
bys idn: gen DeltaFbk_regression=(F.b_regression-b_regression)/k_regression;
bys idn: gen leverage_regression=b_regression/k_regression;
sort idn time;

display "--------------------------------------------------";
display "--------------------------------------------------";
display "Where Was Capital Allocated to?";
bys idn: gen DeltaF8kk_regression=(F8.k_regression-k_regression)/k_regression;
reg DeltaF8kk_regression logprod_regression loga_regression logk_regression if time==1999;

*generate share statistics;
foreach var in "rev" "k" "l"{;
bys time: cumul `var', generate(`var'cum);
bys time: egen denominator=sum(`var');
gen `var'aggregate=denominator;
qui gen indicator=0;
qui replace indicator=1 if `var'cum>=0.8;
bys time: egen numerator=sum(`var'*indicator);
qui gen share20_`var'=numerator/denominator;
drop indicator numer* denomin*;
sort idn time;
};
*generate dispersion statistics;
levelsof time, local(years) ;
foreach var in "logprod" "logl" "logk"{;
qui gen std_`var'=.;
foreach yr of local years {;
qui sum `var' if time==`yr', d;
qui replace std_`var'=r(sd) if time==`yr';
};
};

*generate cross sectional correlations;
levelsof time, local(years) ;
foreach var in  "logk" "loga" "logMRPK" {;
qui gen corr_`var'logprod_cs=.;
foreach yr of local years {;
qui correlate `var' logprod if time==`yr';
qui replace  corr_`var'logprod_cs =r(rho) if time==`yr';
};
sort idn time;
};
levelsof time, local(years) ;
foreach var in "logk" "loga"{;
qui gen corr_`var'logMRPK_cs=.;
foreach yr of local years {;
qui correlate `var' logMRPK if time==`yr';
qui replace  corr_`var'logMRPK_cs =r(rho) if time==`yr';
};
sort idn time;
};

*generate macro variables;
qui gen lnyy=log(yy);
qui gen lnKK=log(KK);
foreach var in "MIS" "std_logMRPK" "lnyy" "lnKK" "KK" "BB"{;
bys idn: egen `var'_99_temp=mean(`var') if time==1999;
bys idn: egen `var'_99=mean(`var'_99_temp);
drop *temp;
qui gen Delta`var'_99=`var'-`var'_99;
drop `var'_99;
};
qui gen DeltaBBDeltaKK_99=DeltaBB_99/DeltaKK_99;


display "--------------------------------------------------";
display "---------- TABLE 4 --------------------------";

display "-------rows 1-3------------------";
display "Standard Deviations";
foreach var in "logprod" "logl" "logk"{;
sum std_`var' if inregression==1;
};
display "-------rows 4-5------------------";
foreach var in  "l" "k"{;
sum share20_`var' if inregression==1;
};
display "-------rows 6-8------------------";
display "Baseline Regressions in Pre-Crisis Period";
qui xtreg DeltaFkk_regression logprod_regression loga_regression logk_regression i.time, fe;
estimates table, drop(i.time) b;
display "-------rows 9-11------------------";
qui xtreg DeltaFbk_regression logprod_regression loga_regression logk_regression i.time, fe;
estimates table, drop(i.time) b;
display "-------row 12------------------";
sum borrower if inregression==1;
display "-------row 13------------------";
qui reg leverage_regression logk_regression i.time;
estimates table, drop(i.time) b;
display "-------rows 14-18------------------";
foreach var in "logk" "loga" "logMRPK"{;
sum corr_`var'logprod_cs if inregression==1;
};
foreach var in "logk" "loga" {;
sum corr_`var'logMRPK_cs if inregression==1;
};



display "--------------------------------------------------";
display "---------- TABLE 5 and 6 (Models) ----------------";
sum DeltaMIS_99 if time==2007;
sum Deltastd_logMRPK_99 if time==2007;
sum Deltalnyy_99 if time==2007;
sum DeltalnKK_99 if time==2007;
sum DeltaBBDeltaKK_99 if time==2007;



